package persistencia;

/**
 * @author BitClub
 */
import bitclub.Tarifa;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class PTarifa {

    /**
     * Introduce un objeto Tarifa en la base de datos 
     */
    public static void nuevoBD(Tarifa tarifa) {
        Connection connection = null;
        Statement statement = null;

        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection(PDatos.baseDatos, PDatos.usuario, PDatos.password);
            statement = connection.createStatement();
            String nombre = tarifa.getNombre();
            double precio = tarifa.getPrecio();
            double recargo = tarifa.getRecargo();
            int num_dias = tarifa.getNumDias();
            int num = statement.executeUpdate("INSERT INTO TARIFAS (NOMBRE,PRECIO,RECARGO,NUM_DIAS) VALUES ('" + nombre + "'," + precio + "," + recargo + "," + num_dias + ")");
        } catch (Exception ex) {
            System.out.println(ex.toString());
        } finally {
            try {
                statement.close();
                connection.close();
            } catch (Exception ex) {
                System.out.println(ex.toString());
            }
        }
    }

    /**
     * Actualiza los datos un objeto Tarifa en la base de datos. 
     */
    public static void actualizarBD(Tarifa tarifa) {
        Connection connection = null;
        Statement statement = null;

        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection(PDatos.baseDatos, PDatos.usuario, PDatos.password);
            statement = connection.createStatement();
            int id = tarifa.getId();
            String nombre = tarifa.getNombre();
            double precio = tarifa.getPrecio();
            double recargo = tarifa.getRecargo();
            int num_dias = tarifa.getNumDias();
            statement.executeUpdate("UPDATE TARIFAS SET NOMBRE='" + nombre + "',PRECIO=" + precio + ",RECARGO=" + recargo + ",NUM_DIAS=" + num_dias + " WHERE ID=" + id);
        } catch (Exception ex) {
            System.out.println(ex.toString());
        } finally {
            try {

                statement.close();
                connection.close();

            } catch (Exception ex) {
                System.out.println(ex.toString());
            }
        }
    }

    /**
     * Devuelve el objeto Tarifa de la base de datos cuyo id corresponde al introducido por teclado. 
     */
    public static Tarifa seleccionarPorIdBD(int id) {

        Tarifa tarifa = null;
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;

        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection(PDatos.baseDatos, PDatos.usuario, PDatos.password);
            statement = connection.createStatement();
            resultSet = statement.executeQuery("SELECT ID, NOMBRE, PRECIO, RECARGO, NUM_DIAS FROM TARIFAS WHERE ID=" + id);

            if (resultSet.next()) {
                tarifa = new Tarifa();
                tarifa.setId(id);
                tarifa.setNombre(resultSet.getString("nombre"));
                tarifa.setNumDias(resultSet.getInt("num_dias"));
                tarifa.setPrecio(resultSet.getDouble("precio"));
                tarifa.setRecargo(resultSet.getDouble("recargo"));
            }

        } catch (Exception ex) {
            System.out.println(ex.toString());
        } finally {
            try {
                resultSet.close();
                statement.close();
                connection.close();

            } catch (Exception ex) {
                System.out.println(ex.toString());
            }
        }

        return tarifa;
    }

    /**
     * Devuelve los datos de los objeto Tarifa cuyo nombre coincide en la base de datos con el introducido por teclado.
     */
    public static List<Tarifa> seleccionarPorNombreBD(String nombre) {

        Tarifa tarifa = null;
        List<Tarifa> listTarifa = new ArrayList();
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;

        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection(PDatos.baseDatos, PDatos.usuario, PDatos.password);
            statement = connection.createStatement();
            resultSet = statement.executeQuery("SELECT ID, NOMBRE, PRECIO, RECARGO, NUM_DIAS FROM TARIFAS WHERE NOMBRE LIKE '%" + nombre + "%'");

            while (resultSet.next()) {
                tarifa = new Tarifa();
                tarifa.setId(resultSet.getInt("id"));
                tarifa.setNombre(resultSet.getString("nombre"));
                tarifa.setNumDias(resultSet.getInt("num_dias"));
                tarifa.setPrecio(resultSet.getDouble("precio"));
                tarifa.setRecargo(resultSet.getDouble("recargo"));
                listTarifa.add(tarifa);
            }

        } catch (Exception ex) {
            System.out.println(ex.toString());
        } finally {
            try {
                resultSet.close();
                statement.close();
                connection.close();
            } catch (Exception ex) {
                System.out.println(ex.toString());
            }

        }

        return listTarifa;
    }

    /**
     * Elimina un objeto Tarifa de la base de datos. 
     */
    public static void eliminarBD(Tarifa tarifa) {

        Connection connection = null;
        Statement statement = null;


        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection(PDatos.baseDatos, PDatos.usuario, PDatos.password);
            statement = connection.createStatement();
            int id = tarifa.getId();
            statement.executeUpdate("DELETE FROM TARIFAS WHERE ID=" + id);

        } catch (Exception ex) {
            System.out.println(ex.toString());
        } finally {
            try {
                statement.close();
                connection.close();

            } catch (Exception ex) {
                System.out.println(ex.toString());
            }

        }
    }

    public static boolean existeTarifaEnArticuloBD(int tarifa_id) {

        int num = 0;

        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;

        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection(PDatos.baseDatos, PDatos.usuario, PDatos.password);
            statement = connection.createStatement();
            String cadena = "SELECT COUNT(*) AS CONTADOR FROM ARTICULOS WHERE TARIFA_ID = " + tarifa_id;
            resultSet = statement.executeQuery(cadena);

            if (resultSet.next()) {
                num = resultSet.getInt("CONTADOR");
            }

        } catch (Exception ex) {
            System.out.println(ex.toString());
        } finally {

            try {
                resultSet.close();
                statement.close();
                connection.close();

            } catch (Exception ex) {
                System.out.println(ex.toString());
            }
        }
        if (num > 0) {
            return true;
        } else {
            return false;
        }
    }
}
